package com.dashboard.dkmh;



import java.sql.ResultSet;
import java.util.ArrayList;

import com.lb.jwitter.connectDatabase.ConnectPDT;
import com.opensymphony.xwork2.Action;

public class diemMHAction {

	private String jsonData;
	public String getJsonData() {
		return jsonData;
	}


	public void setJsonData(String jsonData) {
		this.jsonData = jsonData;
	}
	////////////////////////////////////////////////////////
	private String namHoc;

	public String getNamHoc() {
		return namHoc;
	}


	public void setNamHoc(String namHoc) {
		this.namHoc = namHoc;
	}
	/////////////////////////////////////////////////////////
	private String hocKy;

	public String getHocKy() {
		return hocKy;
	}


	public void setHocKy(String hocKy) {
		this.hocKy = hocKy;
	}
	/////////////////////////////////////////////////////////
	private String khoa;
	public String getKhoa() {
		return khoa;
	}


	public void setKhoa(String khoa) {
		this.khoa = khoa;
	}
	/////////////////////////////////////////////////////////
	private String boMon;

	public String getBoMon() {
		return boMon;
	}


	public void setBoMon(String boMon) {
		this.boMon = boMon;
	}
	///////////////////////////////////////////////////////////
	private ArrayList<MonHocDiem> listMonHocDiem;

	public String execute() throws Exception
	{
		ConnectPDT connectionOracle= new ConnectPDT();
		if(connectionOracle.getConnection()!=null){
			//hocKy o day co gia tri 20142 : namHoc 2014 , hocKy 2
			/*
			if(namHoc==null||namHoc.length()<1){
				return Action.ERROR;
			}
			*/
			System.out.println("->get Diem MH data");
			System.out.println("+hocKy : " + hocKy);
			System.out.println("+khoa : " + khoa);
			if(hocKy==null||hocKy.length()<1){
				System.out.println("@hocky error");
				return Action.ERROR;
			}
			if(khoa==null||khoa.length()<1){
				System.out.println("@khoa error");
				return Action.ERROR;
			}
			
			listMonHocDiem =new ArrayList<MonHocDiem>();
			String semester=hocKy; //namHoc+hocKy;
			String query="select count (*) as count, DIEM_SV.MA_MH,DM_MON_HOC.TEN_MH"
					 +" from PDT_WEB.DIEM_SV, PDT_WEB.DM_MON_HOC,(select MA_BO_MON, MA_KHOA  from PDT_WEB.DM_BO_MON where MA_KHOA='"+khoa+"') C"
					 + " where  DIEM_SV.MA_MH=DM_MON_HOC.MA_MH and DM_MON_HOC.MA_BO_MON=C.MA_BO_MON and HK_NH = '"+semester+"'"
					 +" GROUP BY (DIEM_SV.MA_MH,DM_MON_HOC.TEN_MH) ORDER BY DIEM_SV.MA_MH";
			
			ResultSet data=connectionOracle.getData(query);
			while(data.next()){
				String maMonHoc=data.getString("MA_MH");
				String tenMonHoc=data.getString("TEN_MH");
				int count=data.getInt("COUNT");
				MonHocDiem monHoc=new MonHocDiem();
				monHoc.setMaMonHoc(maMonHoc);
				monHoc.setTenMonHoc(tenMonHoc);
				monHoc.setTotal(count);
				listMonHocDiem.add(monHoc);
			}
			
			/////////////////////////////////////////////////////////////////////////////////////////
			query="select count (*) as count,DIEM_SV.DIEM_TONG_KET,DIEM_SV.MA_MH"
					+" from PDT_WEB.DIEM_SV, PDT_WEB.DM_MON_HOC,(select MA_BO_MON, MA_KHOA  from PDT_WEB.DM_BO_MON where MA_KHOA='"+khoa+"') C"
					+" where  DIEM_SV.MA_MH=DM_MON_HOC.MA_MH and HK_NH ='"+semester+"' and DM_MON_HOC.MA_BO_MON=C.MA_BO_MON"
					+" GROUP BY (DIEM_SV.DIEM_TONG_KET, DIEM_SV.MA_MH) ORDER BY MA_MH";
			data=connectionOracle.getData(query);
			while(data.next()){
				String maMonHoc=data.getString("MA_MH");
				int count=data.getInt("COUNT");
				String diem=data.getString("DIEM_TONG_KET");
				processData(maMonHoc,count,diem);
			}
			jsonData="[";
			for(int index=0;index<listMonHocDiem.size();index++){
				String maMonHoc=listMonHocDiem.get(index).getMaMonHoc();
				String tenMonHoc=listMonHocDiem.get(index).getTenMonHoc();
				int total=listMonHocDiem.get(index).getTotal();
				jsonData=jsonData+"{\"MA_MH\":\""+maMonHoc+"\",\"TEN_MH\":\""+tenMonHoc+"\",\"total\":"+total+",";
				int other=total;
				for(int index1=0;index1<5;index1++){
					int rangeCount=listMonHocDiem.get(index).listRangeCount[index1];
					jsonData=jsonData+"\"range"+(index1+1)+"\":"+rangeCount+",";
					other=other-rangeCount;
				}
				jsonData=jsonData+"\"other\":"+other+"},";
			}
			//jsonData=jsonData.replaceAll(",}", "}");
			jsonData=jsonData+"]";
			jsonData=jsonData.replaceAll(",]", "]");
			
			System.out.println(jsonData);
			System.out.println("#get diem MH data");
		}
		connectionOracle.closeConnection();
		return Action.SUCCESS;

	}
	
	private void processData(String maMonHoc,int count,String diemString){
		try{
			float diem=Float.parseFloat(diemString);
			
			int index=findDiemMonHoc(maMonHoc);
			if(index>=0){
				if(diem<=3.0){
					listMonHocDiem.get(index).listRangeCount[0]=listMonHocDiem.get(index).listRangeCount[0]+count;
				}
				else if(diem<=5.0){
					listMonHocDiem.get(index).listRangeCount[1]=listMonHocDiem.get(index).listRangeCount[1]+count;
				}
				else if(diem<=7.0){
					
					listMonHocDiem.get(index).listRangeCount[2]=listMonHocDiem.get(index).listRangeCount[2]+count;
				}
				else if(diem<=9.0){
					listMonHocDiem.get(index).listRangeCount[3]=listMonHocDiem.get(index).listRangeCount[3]+count;
				}
				else if(diem<=10.0){
					listMonHocDiem.get(index).listRangeCount[4]=listMonHocDiem.get(index).listRangeCount[4]+count;
				}
			}
		}
		catch(Exception e){
			
		}
	}
	private int findDiemMonHoc(String maMonHoc){
		
		for(int index=0;index<listMonHocDiem.size();index++){
			if(listMonHocDiem.get(index).getMaMonHoc().equals(maMonHoc)){
				return index;
			}
		}
		return -1;
	}
}
